using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace DAO
{
    public class ConnectionString
    {
           

        // protected static String connectionString;

        public string StringConnection()
        { 
           
            return "Server=.; Database='RMS'; Integrated Security=SSPI;";
            
            
        }
        public DataSet ExecuteQuery_DataSet(String spName)
        {
            DataSet dt = new DataSet();
            try
            {
                SqlConnection connect = new SqlConnection(StringConnection());
                connect.Open();
                try
                {
                    SqlCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = spName;
                   
                    //command.Parameters.Add("@isbn", SqlDbType.Int).Value = 1;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(dt);
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        public DataSet ExecuteQuery_SQL_Roi(String sql)
        {

            SqlConnection connect = new SqlConnection(StringConnection());
            connect.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, connect);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        public string ExecuteScalar(String spName, List<SqlParameter> sqlParams)
        {
            string kq;
            try
            {
                SqlConnection connect = new SqlConnection(StringConnection());
                connect.Open();
                try
                {
                    SqlCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = spName;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                   
                    try
                    {
                        kq = command.ExecuteScalar().ToString();
                    }
                    catch { kq = ""; }
                   
                   // string kq1 = command.ExecuteScalar().ToString();
                    
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return kq;
        }
        public int ExecuteScalar(String spName)
        {
            int kq = 0;
            try
            {
                SqlConnection connect = new SqlConnection(StringConnection());
                connect.Open();
                try
                {
                    SqlCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = spName;
                   
                    try
                    {
                        kq = int.Parse(command.ExecuteScalar().ToString());
                    }
                    catch { kq = 0; }
                    command.ExecuteScalar();
                    // string kq1 = command.ExecuteScalar().ToString();

                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return kq;
        }
        #region ExecuteQuery
        public DataTable ExecuteQuery(String spName, List<SqlParameter> sqlParams)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection connect = new SqlConnection(StringConnection());
                connect.Open();
                try
                {
                    SqlCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = spName;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    //command.Parameters.Add("@isbn", SqlDbType.Int).Value = 1;
                   SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(dt);
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        public DataTable ExecuteQuery(String spName)
        {
            return ExecuteQuery(spName, null);
        }
        #endregion
        #region ExecuteNoneQuery
        public int ExecuteNoneQuery(String spName, List<SqlParameter> sqlParams)
        {
            int n;
            try
            {
                SqlConnection connect = new SqlConnection(StringConnection());
                connect.Open();
                try
                {
                    SqlCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = spName;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    n = command.ExecuteNonQuery();
                }

                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    connect.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return n;
        }
        public int ExecuteNoneQuery(String spName)
        {
            return ExecuteNoneQuery(spName, null);
        }
        #endregion

    }
}
